import pymysql
import pymysql.cursors
import sqlite3
import os
import json

# 使用SQLite作为数据库后端
USE_SQLITE = False
SQLITE_DB_PATH = os.path.join(os.getcwd(), 'tone_analysis.db')

# MySQL数据库配置，从环境变量中读取
DB_CONFIG = {
    'host': os.environ.get('MYSQL_HOST', 'localhost'),
    'user': os.environ.get('MYSQL_USER', 'root'),
    'password': os.environ.get('MYSQL_PASSWORD', 'root'),
    'db': os.environ.get('MYSQL_DATABASE', 'shengyuntong'),
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

def init_sqlite_db():
    """初始化SQLite数据库"""
    conn = sqlite3.connect(SQLITE_DB_PATH)
    cursor = conn.cursor()
    
    # 创建标准音频表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS standard_audios (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        character_text TEXT NOT NULL,
        pinyin TEXT NOT NULL,
        tone_type INTEGER NOT NULL,
        audio_path TEXT NOT NULL,
        frequency_data TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(character_text, pinyin)
    )
    """)
    
    # 插入一些测试数据
    test_data = [
        ("一", "yi", 1, "audio_samples/一_yi_1.wav", json.dumps([{"time": 0.1, "frequency": 220.0}, {"time": 0.2, "frequency": 220.0}])),
        ("二", "er", 4, "audio_samples/二_er_4.wav", json.dumps([{"time": 0.1, "frequency": 280.0}, {"time": 0.2, "frequency": 200.0}])),
        ("三", "san", 1, "audio_samples/三_san_1.wav", json.dumps([{"time": 0.1, "frequency": 220.0}, {"time": 0.2, "frequency": 220.0}])),
        ("四", "si", 4, "audio_samples/四_si_4.wav", json.dumps([{"time": 0.1, "frequency": 280.0}, {"time": 0.2, "frequency": 200.0}])),
        ("五", "wu", 3, "audio_samples/五_wu_3.wav", json.dumps([{"time": 0.1, "frequency": 220.0}, {"time": 0.2, "frequency": 180.0}, {"time": 0.3, "frequency": 240.0}]))
    ]
    
    for item in test_data:
        cursor.execute("""
        INSERT OR REPLACE INTO standard_audios 
        (character_text, pinyin, tone_type, audio_path, frequency_data)
        VALUES (?, ?, ?, ?, ?)
        """, item)
    
    conn.commit()
    conn.close()
    print("SQLite数据库初始化完成")

def dict_factory(cursor, row):
    """将SQLite查询结果转换为字典格式"""
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def get_connection():
    """获取数据库连接"""
    if USE_SQLITE:
        # 如果数据库文件不存在，初始化它
        if not os.path.exists(SQLITE_DB_PATH):
            init_sqlite_db()
        
        conn = sqlite3.connect(SQLITE_DB_PATH)
        conn.row_factory = dict_factory
        return conn
    else:
        try:
            connection = pymysql.connect(**DB_CONFIG)
            return connection
        except Exception as e:
            print(f"数据库连接失败: {e}")
            raise

def execute_query(query, params=None):
    """执行SQL查询"""
    connection = get_connection()
    try:
        if USE_SQLITE:
            # 替换MySQL的占位符为SQLite的占位符
            query = query.replace("%s", "?")
            cursor = connection.cursor()
            cursor.execute(query, params or ())
            result = cursor.fetchall()
            return result
        else:
            with connection.cursor() as cursor:
                cursor.execute(query, params)
                result = cursor.fetchall()
                return result
    finally:
        connection.close()

def execute_update(query, params=None):
    """执行SQL更新操作（INSERT, UPDATE, DELETE等）"""
    connection = get_connection()
    try:
        if USE_SQLITE:
            # 替换MySQL的占位符为SQLite的占位符
            query = query.replace("%s", "?")
            cursor = connection.cursor()
            cursor.execute(query, params or ())
            connection.commit()
            return cursor.rowcount
        else:
            with connection.cursor() as cursor:
                rows_affected = cursor.execute(query, params)
                connection.commit()
                return rows_affected
    finally:
        connection.close()

def execute_many(query, params_list):
    """批量执行SQL操作"""
    connection = get_connection()
    try:
        if USE_SQLITE:
            # 替换MySQL的占位符为SQLite的占位符
            query = query.replace("%s", "?")
            cursor = connection.cursor()
            cursor.executemany(query, params_list)
            connection.commit()
            return cursor.rowcount
        else:
            with connection.cursor() as cursor:
                rows_affected = cursor.executemany(query, params_list)
                connection.commit()
                return rows_affected
    finally:
        connection.close()

# 如果直接运行此文件，初始化SQLite数据库
if __name__ == "__main__":
    if USE_SQLITE:
        init_sqlite_db() 